﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Common.Utilities;

namespace Workflows.Components.Entities.Relations.InferredAssociations
{
    public class AttributeValueDistribution<T> where T : IComparable
    {
        private SqlDbType _DbType;
        private int _DistinctCount;
        private double _NonNullValueCoverage;
        private int _NonNullValueCount;
        private T _Median;
        private T _Max;
        private T _Min;
        private string _Mean;
        private double? _StandardDeviation;

        #region ctor
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="nonNullCount"></param>
        /// <param name="distinctCount"></param>
        /// <param name="coverage"></param>
        /// <param name="min"></param>
        /// <param name="max"></param>
        /// <param name="median"></param>
        /// <param name="mean"></param>
        /// <param name="sd"></param>
        public AttributeValueDistribution(
            SqlDbType dbType,
            int nonNullCount,
            int distinctCount,
            double coverage,
            T min, T max, T median,
            string mean,
            double? sd)
        {
            this._DbType = dbType;
            this._NonNullValueCount = nonNullCount;
            this._DistinctCount = distinctCount;
            this._NonNullValueCoverage = coverage;
            this._Min = min;
            this._Max = max;
            this._Median = median;
            this._Mean = mean;
            this._StandardDeviation = sd;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="fieldName"></param>
        public AttributeValueDistribution(string connStr, string tblName, string fieldName)
        {
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr, string.Empty, tblName, fieldName);
            if (FieldDataType.IsBinaryType(col.DbType) || FieldDataType.IsTextType(col.DbType))
            {
                throw new Exception("Unable to determine field stats for " + col.DbType.ToString());
            }
            if (!DbUtil.IsTableColumnIndexed(connStr, tblName, fieldName))
            {
                DbUtil.AddIndex(connStr, tblName, fieldName, "idx_" + tblName + "_" + fieldName);
            }
            this._DbType = col.DbType;

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = string.Format("select count (*) from {0}", tblName);
                int totalRecords = int.Parse(cmd.ExecuteScalar().ToString());
                if (totalRecords > 0)
                {
                    cmd.CommandText = string.Format("select count (*) from {0} where {1} is not null", tblName,
                                                    fieldName);
                    this._NonNullValueCount = int.Parse(cmd.ExecuteScalar().ToString());
                    this._NonNullValueCoverage = ((double)this._NonNullValueCount) / totalRecords;
                    if (this._NonNullValueCount > 0)
                    {
                        cmd.CommandText = string.Format("select count (distinct {1}) from {0} where {1} is not null",
                                                        tblName,
                                                        fieldName);
                        this._DistinctCount = int.Parse(cmd.ExecuteScalar().ToString());

                        List<T> values = new List<T>();
                        cmd.CommandText = string.Format("select {1} from {0} where {1} is not null order by {1} asc",
                                                        tblName, fieldName);
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            values.Add((T)reader[0]);
                        }
                        reader.Close();
                        this._Min = values[0];
                        this._Max = values[values.Count - 1];
                        this._Median = values[values.Count / 2];


                        if (FieldDataType.IsNumber(col.DbType))
                        {
                            double total = 0;
                            foreach (T item in values)
                            {
                                total += double.Parse(item.ToString());
                            }
                            this._Mean = (total / values.Count).ToString();
                            double sqreSum = 0;
                            foreach (T item in values)
                            {
                                sqreSum += Math.Pow((double.Parse(item.ToString()) - double.Parse(this._Mean)), 2);
                            }
                            this._StandardDeviation = Math.Sqrt(sqreSum / (Math.Max(1, values.Count - 1)));

                        }
                        else if (FieldDataType.IsDateTime(col.DbType))
                        {
                            int totalDaysSince = 0;
                            DateTime dateFrom = DateTime.Parse(this._Min.ToString());
                            foreach (T item in values)
                            {
                                DateTime dateValue = DateTime.Parse(item.ToString());
                                TimeSpan span = new TimeSpan(dateValue.Ticks - dateFrom.Ticks);
                                totalDaysSince += span.Days;
                            }
                            DateTime dateAvg = dateFrom.Add(new TimeSpan(totalDaysSince / values.Count, 0, 0));
                            this._Mean = dateAvg.ToString();
                            double sqrSum = 0;
                            foreach (T item in values)
                            {
                                sqrSum += Math.Pow((DateTime.Parse(item.ToString()) - dateAvg).Days, 2);
                            }
                            this._StandardDeviation = Math.Sqrt(sqrSum / (Math.Max(1, values.Count - 1)));

                        }
                        else if (FieldDataType.IsStringType(col.DbType))
                        {
                            this._Mean = null;
                            double? nullDouble = new double();
                            this._StandardDeviation = nullDouble;

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region props
        /// <summary></summary>
        public int DistinctCount
        {
            get { return _DistinctCount; }
            set { _DistinctCount = value; }
        }
        /// <summary></summary>
        public double NonNullValueCoverage
        {
            get { return _NonNullValueCoverage; }
            set { _NonNullValueCoverage = value; }
        }
        /// <summary></summary>
        public int NonNullValueCount
        {
            get { return _NonNullValueCount; }
            set { _NonNullValueCount = value; }
        }
        /// <summary></summary>
        public string Mean
        {
            get { return _Mean; }
            set { _Mean = value; }
        }
        /// <summary></summary>
        public T Median
        {
            get { return _Median; }
            set { _Median = value; }
        }
        /// <summary></summary>
        public T Max
        {
            get { return _Max; }
            set { _Max = value; }
        }
        /// <summary></summary>
        public T Min
        {
            get { return _Min; }
            set { _Min = value; }
        }
        /// <summary></summary>
        public double? StandardDeviation
        {
            get { return _StandardDeviation; }
            set { _StandardDeviation = value; }
        }
        /// <summary></summary>
        public SqlDbType DbType
        {
            get { return _DbType; }
            set { _DbType = value; }
        }
        #endregion
    }
}
